package com.excelexport.service.impl;

import com.excelexport.entity.Lottery;
import com.excelexport.service.MiReportService;
import com.excelexport.utils.BeanConvert;
import com.excelexport.utils.ConvertResultSetToEntity;
import com.excelexport.utils.easyexcel.EasyExcelUtil;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.poifs.crypt.EncryptionInfo;
import org.apache.poi.poifs.crypt.EncryptionMode;
import org.apache.poi.poifs.crypt.Encryptor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Service;
import org.springframework.util.StreamUtils;

import javax.swing.filechooser.FileSystemView;
import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

/**
 * @author Agus
 */
@Service
public class MiReportServiceImpl implements MiReportService {
    @Override
    public void exportExcel(String key) throws Exception {
        List<Lottery> lotteryList = executeSQL(key);
        if (lotteryList.size() == 0) {
            throw new Exception("导出Excel为空");
        }
        //获取当前桌面地址
        FileSystemView fsv = FileSystemView.getFileSystemView();
        File com = fsv.getHomeDirectory();
        //时间格式
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        //输出文件路径+名
        String excelFilePath = com.getPath() + "/新建XLSX工作表" + sdf.format(new Date()) + ".xlsx";
        EasyExcelUtil.simpleWrite(excelFilePath, "Sheet1", null, lotteryList, Lottery.class);
        fileEncryption(excelFilePath,"1234");
    }

    /**
     * 执行SQL返回查询结果
     *
     * @param key
     * @return
     * @throws Exception
     */
    private List<Lottery> executeSQL(String key) throws Exception {
        ClassPathResource resource = new ClassPathResource("test.sql");
        InputStream inputStream = resource.getInputStream();
        String sql = StreamUtils.copyToString(inputStream, StandardCharsets.UTF_8);
        inputStream.close();
        String url = "jdbc:mysql://localhost:3306/mysql?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false&allowPublicKeyRetrieval=true";
        String user = "root";
        Connection conn = DriverManager.getConnection(url, user, key);
        PreparedStatement prepareStatement = conn.prepareStatement(sql);
        prepareStatement.setInt(1, 500);
        ResultSet rs = prepareStatement.executeQuery();
        Object[] objects = ConvertResultSetToEntity.parseDataEntityBeans(rs, Lottery.class);
        //关闭数据库连接
        conn.close();
        List<Lottery> lotteryList = BeanConvert.objectConvertBean(Arrays.asList(objects), Lottery.class);
        return lotteryList;
    }

    /**
     * 加密
     * @param excelFilePath
     * @throws Exception
     */
    private void fileEncryption(String excelFilePath,String passWord) throws Exception {
        POIFSFileSystem fs = new POIFSFileSystem();
        EncryptionInfo info = new EncryptionInfo(EncryptionMode.standard);
        Encryptor enc = info.getEncryptor();
        //设置密码
        enc.confirmPassword(passWord);
        //加密文件
        OPCPackage opc = OPCPackage.open(new File(excelFilePath), PackageAccess.READ_WRITE);
        OutputStream os = enc.getDataStream(fs);
        opc.save(os);
        opc.close();

        //把加密后的文件写回到流
        FileOutputStream fos = new FileOutputStream(excelFilePath);
        fs.writeFilesystem(fos);
        fos.flush();
    }
}
